create database GoodsDB
on
(
   name='GoodsDB',
   filename='D:\GoodsDB.mdf',
   size=50,
   maxsize=100,
   filegrowth=10
)
log on
(
   name='GoodsDB_log',
   filename='D:\GoodsDB_log.ldf',
   size=50,
   maxsize=100,
   filegrowth=10
)
go
create table GoodsType
(
   TypeID int not null primary key identity(1,1),
   TypeName nvarchar(20) not null 
)
create table GoodsInfo
(
   GoodsID int not null primary key identity(1,1),
   GoodsName nvarchar(20) not null,
   GoodsColor nvarchar(20) not null,
   GoodsBrand nvarchar(20),
   GoodsMoney money not null,
   TypeID int not null references GoodsType(TypeID)
)


--2、使用插入语句为两张表添加数据
--商品类型表（GoodsType）
--商品类型编号 商品类型名称
--1 服装内衣
--2 鞋包配饰
--3 手机数码
 
insert into GoodsType values ('服装内衣'),('鞋包配饰'),('手机数码')
select * from GoodsType
--商品信息表（GoodsType）
--商品编号 商品名称 商品颜色 商品品牌 商品价格 商品类型编号
--1 提花小西装 红色 菲曼琪 300 1
--2 百搭短裤 绿色 哥弟 100 1
--3 无袖背心 白色 阿依莲 700 1
--4 低帮休闲鞋 红色 菲曼琪 900 2
--5 中跟单鞋 绿色 哥弟 400 2
--6 平底鞋 白色 阿依莲 200 2
--7 迷你照相机 红色 尼康 500 3
--8 硬盘 黑色 希捷 600 3
--9 显卡 黑色 技嘉 800 3

insert into GoodsInfo values ('提花小西装','红色','菲曼琪','300','1'),('百搭短裤','绿色','哥弟','100','1'),('无袖背心','白色','阿依莲','700','1'),
('低帮休闲鞋','红色','菲曼琪','900','2'),('中跟单鞋','绿色','哥弟','400','2'),
('平底鞋','白色','阿依莲','200','2'),('迷你照相机','红色','尼康','500','3'),('硬盘','黑色','希捷','600','3'),('显卡','黑色','技嘉','800','3')

select * from GoodsType
select * from GoodsInfo
--3、查询价格最贵的商品名称，商品颜色和商品价格，要求使用别名显示列名
select TypeName 商品名称,GoodsColor 商品颜色,GoodsMoney 商品价格 from GoodsType inner join GoodsInfo on GoodsType.TypeID = GoodsInfo.TypeID
where GoodsMoney=(select MAX(GoodsMoney) from GoodsInfo)
--4、按商品类型编号分组查询商品最高价格，最低价格和平均价格，要求使用别名显示列名
select GoodsInfo.TypeID 商品类型编号,MAX(GoodsMoney)最高价格,MIN(GoodsMoney)最低价格,AVG(GoodsMoney)平均价格 from GoodsInfo group by GoodsInfo.TypeID
--5、查询商品信息所有列，要求商品颜色为红色，价格在300~600之间
select * from GoodsType inner join GoodsInfo on GoodsType.TypeID = GoodsInfo.TypeID
where GoodsColor='红色' and GoodsMoney>=300 and GoodsMoney<=600
--6、查询每类商品的平均价格，要显示商品的类别名称和平均价格。
select TypeName 类别名称,AVG(GoodsMoney)平均价格 from GoodsType inner join GoodsInfo on GoodsType.TypeID = GoodsInfo.TypeID group by TypeName
--7、查询每种颜色的商品数
select GoodsColor 颜色,COUNT(GoodsColor)商品数 from GoodsInfo group by GoodsColor
--8、给商品类型表添加一个“备注”字段，并给所有记录的备注都填上“我是商品类型信息备注”
alter table GoodsType add Typetext text not null default('我是商品类型信息备注')
--9、在商品信息表中删除商品类型的外键约束
alter table GoodsInfo
drop constraint FK__GoodsInfo__TypeI__1273C1CD

--10、删除所有商品类型记录

delete from GoodsType --清除表数据
drop table GoodsInfo  --删除表


